package com.monster.dao;

import com.monster.pojo.*;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PlaylistDaoImpl extends BaseDao implements PlaylistDao {

    //查询所有的歌单,按播放量降序
    public List<Playlist> findAll(int page) {
        //创建一个集合接收所有歌单
        List<Playlist> list = new ArrayList<Playlist>();
        //定义SQL语句
        String sql = "select * from Playlist,user where Playlist.user_id=user.user_id ORDER BY Playlist_playcount desc limit ?,20";
        Object[] obj = {(page-1)*20};
        //调用查询方法
        rs = this.executeQuery(sql,obj);
        Playlist Playlist = null;
        User user = null;
        try {
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("user_name"));

                Playlist = new Playlist();
                Playlist.setPlaylistId(rs.getLong("Playlist_id"));
                Playlist.setPlaylistName(rs.getString("Playlist_name"));
                Playlist.setUserId(rs.getLong("user_id"));
                Playlist.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                Playlist.setPlaylistPoster(rs.getString("Playlist_poster"));
                Playlist.setUser(user);
                list.add(Playlist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //获取歌单总数量、标签对应歌单的数量
    @Override
    public int getTotalCount(Tag tag,String orderType){
        int totalCount=0;
        String sql="select count(1) from playlist";

        List<Object> list = new ArrayList<Object>();
        if(tag!=null){
            sql += " ,playlist_tag,tag where playlist_tag.playlist_id=playlist.playlist_id and playlist_tag.tag_id=tag.tag_id and tag.tag_id=? ";
            list.add(tag.getTagId());
        }
        if(orderType!=null){
            sql +=" ORDER BY playlist_playcount desc";
        }
        Object[] obj=new Object[list.size()];
        for(int i = 0;i<list.size();i++){
            obj[i]=list.get(i);
        }
        ResultSet rs=this.executeQuery(sql, obj);
        try {
            while(rs.next()){
                totalCount=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return totalCount;
    }

    //查询所有的歌单,按播放量降序
    @Override
    public List<Playlist> findAll(int page,int size) {
        //创建一个集合接收所有歌单
        List<Playlist> list = new ArrayList<Playlist>();
        //定义SQL语句
        String sql = "select * from Playlist,user where Playlist.user_id=user.user_id limit ?,?";
        Object[] obj = {(page-1)*size,size};
        //调用查询方法
        rs = this.executeQuery(sql,obj);
        Playlist Playlist = null;
        User user = null;
        try {
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("user_name"));
                user.setHead(rs.getString("user_head"));

                Playlist = new Playlist();
                Playlist.setPlaylistProfile(rs.getString("playlist_profile"));
                Playlist.setPlaylistId(rs.getLong("Playlist_id"));
                Playlist.setPlaylistName(rs.getString("Playlist_name"));
                Playlist.setUserId(rs.getLong("user_id"));
                Playlist.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                Playlist.setPlaylistPoster(rs.getString("Playlist_poster"));
                Playlist.setUser(user);
                list.add(Playlist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //根据歌单ID   Playlist_id查歌单信息；
    public Playlist getPlaylistByPlaylistId(Long playlistId) {
        String sql = "select * from Playlist,user where Playlist.user_id=user.user_id and Playlist_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        Playlist p = null;
        try {
            while(rs.next()){
                User user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("user_name"));

                p = new Playlist();
                p.setPlaylistId(rs.getLong("Playlist_id"));
                p.setPlaylistName(rs.getString("Playlist_name"));
                p.setUserId(rs.getLong("user_id"));
                p.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                p.setPlaylistProfile(rs.getString("Playlist_profile"));
                p.setPlaylistPoster(rs.getString("Playlist_poster"));
                p.setUser(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }
    @Override
    //通过歌单名 模糊 查 歌单信息；按播放量降序排列
    public List<Playlist> selectPlaylist(String name) {
        ArrayList<Playlist> list = new ArrayList<Playlist>();
        String sql = "select * from Playlist,user where Playlist.user_id=user.user_id and Playlist_name like '%"+ name +"%' ORDER BY Playlist_playcount desc";
        Object[] obj = null;
        rs = this.executeQuery(sql,obj);
        User user = null;
        try {
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("user_name"));
                Playlist playlist = new Playlist();
                playlist.setPlaylistId(rs.getLong("Playlist_id"));
                playlist.setPlaylistName(rs.getString("Playlist_name"));
                playlist.setUserId(rs.getLong("user_id"));
                playlist.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                playlist.setPlaylistPoster(rs.getString("Playlist_poster"));
                playlist.setUser(user);

                list.add(playlist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    // 歌单的增方法；
    public int addPlaylist(Playlist Playlist) {
        String sql = "insert into Playlist values(default,?,?,default,?,'images/playlist/a320.jpg');";
        Object[] obj = {Playlist.getPlaylistName(),Playlist.getUserId(),Playlist.getPlaylistProfile()};
        int result = this.executeUpdate(sql,obj);
        return result;
    }
    //歌单的删方法,通过PlaylistId删除
    public int deletePlaylist(Long playlistId) {
        String sql = "delete from Playlist where Playlist_id=?";
        Object[] obj = {playlistId};
        int result = this.executeUpdate(sql,obj);
        return result;
    }
    /**
     *  //通过歌单ID的修改歌单方法
     * @param Playlist 歌单ID
     * @return
     */
    public int updatePlaylist(Playlist Playlist) {
        String sql = "update Playlist set Playlist_name=?,Playlist_profile=?,Playlist_poster=? where Playlist_id=?";
        Object[] obj = {Playlist.getPlaylistName(),Playlist.getPlaylistProfile(),Playlist.getPlaylistPoster(),Playlist.getPlaylistId()};
        int result = this.executeUpdate(sql,obj);
        return result;
    }
    /**
     * //通过标签查询歌单集合,返回不为空的歌单，根据用户选中最新/最热降序排列
     * @param tag 标签ID
     * @param page  分页页码
     * @return playlist集合，用户id
     */
    public List<Playlist> selectPlaylistByTag(Tag tag, String orderType,int page,int size) {
        Playlist Playlist = null;
        User user = null;
        List<Playlist> list = new ArrayList<Playlist>();
        String sql = "SELECT * FROM Playlist,Playlist_tag,song_playlist,USER WHERE playlist.`user_id` = `user`.`user_id` " +
                "AND Playlist.Playlist_id=Playlist_tag.Playlist_id AND playlist.`playlist_id` = song_playlist.`playlist_id` ";
        ArrayList<Object> oList = new ArrayList<Object>();
        if(tag!=null&&orderType==null){
            sql += " and Playlist_tag.tag_id=?  GROUP BY playlist_name HAVING COUNT(song_playlist.song_id)>0 ";
            oList.add(tag.getTagId());
        }
        if(orderType!=null){
            if(orderType.equals("最新")){
                sql += " ORDER BY playlist.`playlist_id` DESC";
            }else if(orderType.equals("最热")){
                sql += " GROUP BY playlist.playlist_id HAVING COUNT(*) order by playlist_playcount desc";
            }
        }
        if(page>0){
            sql += " limit ?,?";
            oList.add((page-1)*size);
            oList.add(size);
        }
        Object[] params = new Object[oList.size()];
        for(int i =0;i<oList.size();i++){
            params[i]=oList.get(i);
        }
        rs = this.executeQuery(sql,params);
        try {
            while(rs.next()){
                user =new User();
                user.setName(rs.getString("user_name"));
                user.setId(rs.getLong("user_id"));
                user.setHead(rs.getString("user_head"));
                Playlist = new Playlist();
                Playlist.setPlaylistId(rs.getLong("Playlist_id"));
                Playlist.setPlaylistName(rs.getString("Playlist_name"));
                Playlist.setUserId(rs.getLong("user_id"));
                Playlist.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                Playlist.setPlaylistProfile(rs.getString("Playlist_profile"));
                Playlist.setPlaylistPoster(rs.getString("Playlist_poster"));
                Playlist.setUser(user);
                list.add(Playlist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * //通过歌曲ID，歌单ID删除歌单里面的歌曲
     * @param Playlist 歌单ID
     * @param songId  歌曲ID
     * @return
     */
    public int deletePlayListSongBySongId(Playlist Playlist, Long songId){
        String sql = "delete song_Playlist from song_Playlist,Playlist where song_Playlist.Playlist_id=Playlist.Playlist_id and song_Playlist.song_id=? and Playlist.Playlist_id=?";
        Object[] obj = {songId,Playlist.getPlaylistId()};
        int result = this.executeUpdate(sql,obj);
        return result;
    }
    /**
     * //给歌单增加歌曲
     *  Playlist 歌单的ID
     *  songId     歌曲的ID
     * return
     */
    public int addPlaylistSong(Playlist Playlist, Long songId) {
        String sql = "insert into song_Playlist values(default,?,?)";
        Object[] obj = {Playlist.getPlaylistId(),songId};
        int result = this.executeUpdate(sql,obj);
        return result;
    }

    /**
     * 通过歌单ID，查询song_playlist获得歌曲数量
     * @param playlistId
     * @return 歌单中的歌曲数量
     */
    @Override
    public int getSongCount(Long playlistId) {
        String sql = "select count(1) from song_playlist where playlist_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        int result = 0;
        try {
            while(rs.next()){
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 通过歌单ID，获得创建人的id和name
     * @param playlistId
     * @return user的ID、name信息
     */
    @Override
    public User getFounderByPlaylistId(Long playlistId) {
        String sql = "select u.user_id,user_name from user u,playlist p where p.user_id=u.user_id and playlist_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        User user = null;
        try {
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong(1));
                user.setName(rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public ArrayList<Playlist> listCreatedPlaylistByUserId(Long userId, int page) {
        ArrayList<Playlist> playlists = new ArrayList<>();
        String sql = "SELECT p.playlist_id,COUNT(1),playlist_name,playlist_poster,playlist_playcount FROM song_playlist sp " +
                "LEFT JOIN playlist p ON sp.playlist_id=p.playlist_id " +
                "GROUP BY p.playlist_id HAVING p.playlist_id IN (SELECT playlist_id FROM playlist WHERE user_id=?)" +
                "ORDER BY p.playlist_id DESC LIMIT ?,20";
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql,obj);
        try {
            while(rs.next()){
                Playlist p = new Playlist();
                p.setPlaylistId(rs.getLong(1));
                p.setSongCount(rs.getInt(2));
                p.setPlaylistName(rs.getString(3));
                p.setPlaylistPoster(rs.getString(4));
                p.setPlaylistPlaycount(rs.getLong(5));
                playlists.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return playlists;
    }


    //下面的方法用来查询歌单的详细信息
    /**
     * 通过歌单ID 查询歌单中的所有歌曲
     * 返回一个歌曲的集合
     *
     */
    public ArrayList<Song> selectSongIdByPlaylistId(Long playlistId){
        ArrayList<Song> list = new ArrayList<Song>();
        String sql = "select * from Playlist,song_Playlist,song where Playlist.Playlist_id=song_Playlist.Playlist_id and song_Playlist.song_id=song.song_id and Playlist.Playlist_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        Song song = null;
        try {
            while(rs.next()){
                Album album = new Album();
                album.setAlbumId(rs.getLong("album_id"));

                song = new Song();
                song.setSongId(rs.getLong("song_id"));
                song.setSongName(rs.getString("song_name"));
                song.setPoster(rs.getString("song_poster"));
                song.setUrl(rs.getString("song_url"));
                song.setLyric(rs.getString("song_lyric"));
                song.setAlbum(album);
                song.setTime(rs.getString("song_time"));
                list.add(song);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
     * 通过歌单ID，查询所对应的标签集合
     * 返回一个标签ID集合
     *
     */
    public ArrayList<Tag> selectTagByPlaylistId(Long playlistId){
        ArrayList<Tag> list = new ArrayList<Tag>();
        String sql = "select * from Playlist,Playlist_tag,tag where Playlist.Playlist_id=Playlist_tag.Playlist_id and Playlist_tag.tag_id=tag.tag_id and Playlist.Playlist_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        Tag tag = null;
        try {
            while(rs.next()){
               tag = new Tag();
               tag.setTagId(rs.getLong("tag_id"));
               tag.setTagName(rs.getString("tag_name"));
               tag.setTagType(rs.getString("tag_type"));
               list.add(tag);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过歌单ID=like_id，like_type=2(歌单)获取歌单的收藏数量
     */
    public Long selectCollectAmountByPlaylistId(Long playlistId){
        //定义初始收藏数量=0
        long count = 0;
        String sql = "select count(1) from user_like where like_type=2 and like_id=?";
        Object[] obj = {playlistId};
        rs = this.executeQuery(sql,obj);
        try {
            while(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 通过歌单ID=type_id  comment_type=2(2代表歌单评论的类型)  查询歌单ID对应评论总条数
     */
    public Long selectCommentCountByPlaylistId(Long playlistId){
        String sql ="select count(1) from `comment` where comment_type=2 and type_id=?";
        Object[] obj ={playlistId};
        //评论总数
        long commentCount = 0;
        rs = this.executeQuery(sql,obj);
        try {
            while(rs.next()){
                commentCount= rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return commentCount;
    }

    @Override
    /**
     * 通过标签分类查询标歌单
     */
    public List<Playlist> selectPlaylistByTagType(Tag tag,int page){
        List<Playlist> list = new ArrayList<Playlist>();
        String sql = "select * from `playlist`,playlist_tag,tag,user where playlist.playlist_id=playlist_tag.playlist_id and playlist.user_id=user.user_id and playlist_tag.tag_id=tag.tag_id and tag.tag_type=? limit ?,5";
        Object[] obj = {tag.getTagType(),(page-1)*5};
        Playlist playlist = null;
        User user = null;
        rs = this.executeQuery(sql,obj);
        try {
            while(rs.next()){
                user = new User();
                user.setId(rs.getLong("user_id"));
                user.setName(rs.getString("user_name"));
                user.setHead(rs.getString("user_head"));

                playlist = new Playlist();
                playlist.setPlaylistProfile(rs.getString("playlist_profile"));
                playlist.setPlaylistId(rs.getLong("Playlist_id"));
                playlist.setPlaylistName(rs.getString("Playlist_name"));
                playlist.setUserId(rs.getLong("user_id"));
                playlist.setPlaylistPlaycount(rs.getLong("Playlist_playcount"));
                playlist.setPlaylistPoster(rs.getString("Playlist_poster"));
                playlist.setUser(user);
                list.add(playlist);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //通过用户ID查找用户创建的歌单
    @Override
    public ArrayList<Playlist> listSetPlaylistByUserId(Long userId, int page) {
        ArrayList<Playlist> playlists = new ArrayList<>();
        String sql = "SELECT p.playlist_id,Count(1),playlist_name,playlist_poster,playlist_playcount FROM  playlist p GROUP BY p.playlist_id HAVING p.playlist_id IN (SELECT playlist_id FROM playlist WHERE user_id=?)ORDER BY p.playlist_id DESC LIMIT ?,20";
        Object[] obj = {userId, (page-1)*20};
        rs = this.executeQuery(sql,obj);
        try {
            while(rs.next()){
                Playlist p = new Playlist();
                p.setPlaylistId(rs.getLong(1));
                p.setSongCount(rs.getInt(2));
                p.setPlaylistName(rs.getString(3));
                p.setPlaylistPoster(rs.getString(4));
                p.setPlaylistPlaycount(rs.getLong(5));
                playlists.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return playlists;
    }

}
